package com.are.odisea;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class GestionPersonal {
	

	private db conexion = null;
	private Personal personal;
	
	public static final int FILTRO_POR_NOMBRE = 1;
	public static final int FILTRO_POR_CODIGO = 2;
	
	public Personal getPersonal() {
		return personal;
	}

	public void setPersonal(Personal personal) {
		this.personal = personal;
	}

	public GestionPersonal(db conexion) {
		super();
		this.conexion = conexion;
	}
	
	public ArrayList<Personal> Buscar(String criterio) throws SQLException {
		ArrayList<Personal> lista = new ArrayList<Personal>();
		String sql="select * from personal where persnomb like ? or persapel like ?";
		java.sql.PreparedStatement pst = conexion.getConnection().prepareStatement(sql);
		pst.setString(1, "%" +criterio.trim() +"%");
		pst.setString(2, "%" +criterio.trim() +"%");
		ResultSet rs = conexion.Query(pst);
		while (rs.next()) {
			Personal personal = new Personal();
			personal.setCodigo(rs.getString("perscodi"));
			personal.setNombre(rs.getString("persnomb"));
			personal.setApellido(rs.getString("persapel"));
			personal.setDireccion(rs.getString("persdire"));
			personal.setTelefono(rs.getString("perstel1"));
			personal.setMovil(rs.getString("perstel2"));
			personal.setEstado(rs.getInt("persesta"));
			personal.setCargo(rs.getString("capedesc"));
			lista.add(personal);
		}
		
		return lista;
	}
	
	public ArrayList<Personal> Buscar(String criterio, int filter) throws SQLException {
		ArrayList<Personal> lista = new ArrayList<Personal>();
		ResultSet rs = null;
		String sql = "";
		java.sql.PreparedStatement pst = null;
		switch (filter) {
		case FILTRO_POR_NOMBRE:
			sql="select * from personal where persnomb like ? or persapel like ? order by persnomb,persapel";
			pst = conexion.getConnection().prepareStatement(sql);
			pst.setString(1, "%" +criterio.trim() +"%");
			pst.setString(2, "%" +criterio.trim() +"%");
			rs = conexion.Query(pst);
			break;
			
		case FILTRO_POR_CODIGO:
			sql="select * from personal where perscodi like ? order by persnomb,persapel";
			pst = conexion.getConnection().prepareStatement(sql);
			pst.setString(1, "%" +criterio.trim() +"%");
			rs = conexion.Query(pst);

			break;
		default:
			sql="select * from personal where persnomb like ? or persapel like ? order by persnomb,persapel";
			pst = conexion.getConnection().prepareStatement(sql);
			pst.setString(1, "%" +criterio.trim() +"%");
			pst.setString(2, "%" +criterio.trim() +"%");
			rs = conexion.Query(pst);
		}

		while (rs.next()) {
			Personal personal = new Personal();
			personal.setCodigo(rs.getString("perscodi"));
			personal.setNombre(rs.getString("persnomb"));
			personal.setApellido(rs.getString("persapel"));
			personal.setDireccion(rs.getString("persdire"));
			personal.setTelefono(rs.getString("perstel1"));
			personal.setMovil(rs.getString("perstel2"));
			personal.setEstado(rs.getInt("persesta"));
			personal.setCargo(rs.getString("capedesc"));
			lista.add(personal);
		}
		if (rs != null) {
			rs.close();
		}
		return lista;
	}
	
	public boolean Find(String cedula) throws SQLException {
		boolean result = false;
		String sql="select * from personal where perscodi= ?";
		java.sql.PreparedStatement pst = conexion.getConnection().prepareStatement(sql);
		pst.setString(1,cedula.trim());
		ResultSet rs = conexion.Query(pst);
		personal = new Personal();
		if (rs.next()) {
			
			personal.setCodigo(rs.getString("perscodi"));
			personal.setNombre(rs.getString("persnomb"));
			personal.setApellido(rs.getString("persapel"));
			personal.setDireccion(rs.getString("persdire"));
			personal.setTelefono(rs.getString("perstel1"));
			personal.setMovil(rs.getString("perstel2"));
			personal.setEstado(rs.getInt("persesta"));
			personal.setCargo(rs.getString("capedesc"));
			result = true;
		}
		
		return result;
	}
	
	public String getCuadrilla(String cedula) throws SQLException {
		String cuadrilla = "-1";
		String sql ="select cupecuad from view_cuadrilla where cupepers=?";
		java.sql.PreparedStatement pst = conexion.getConnection().prepareStatement(sql);
		pst.setString(1,cedula.trim());
		ResultSet rs = conexion.Query(pst);
		if (rs.next()) {
			cuadrilla = rs.getString("cupecuad");
		}
		rs.close();
		return cuadrilla;
	}

}
